package bean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class ScoreDTO{
	private ScoreVo vo;
	private Connection conn;
	public ScoreDTO(){
		conn = new MyOracle().getConn();
		
	}
	
	public boolean insert(){
		boolean b=false;
		String sql="insert into score(rno,mid,grade, point)"+"values(seq_score_rno.nextval,?,?,?)";
		try{
			PreparedStatement pst =  conn.prepareStatement(sql);
			pst.setString(1, vo.getMid());
			pst.setString(2, vo.getGrade());
			pst.setInt(3,vo.getPoint());
			int cnt = pst.executeUpdate();
			if(cnt>0) b=true;
			
			pst.close();
			conn.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
		return b;
	}
	
	public ArrayList<ScoreVo> select(){
		ArrayList<ScoreVo> al=new ArrayList<>();
		String sql = "select * from score"+"where mid like ? or exam like?";
		try{
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, "%" + vo.getFind());
			pst.setString(2,"%" + vo.getFind());
			
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				ScoreVo v = new ScoreVo();
				v.setMid(rs.getString("mid"));
				v.setGrade(rs.getString("grade"));
				v.setPoint(rs.getInt("point"));
				v.setRno(rs.getInt("rno"));
				al.add(v);
			}
			
			rs.close();
			pst.close();
			conn.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return al;
	}
	
	public ScoreVo selectOne(){
		ScoreVo v = new ScoreVo();
		String sql = "select * from score where rno=?";
		try{
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setInt(1, vo.getRno());
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				v.setMid(rs.getString("mid"));
				v.setGrade(rs.getString("grade"));
				v.setPoint(rs.getInt("point"));
				v.setRno(rs.getInt("rno"));
				v.setExam(rs.getString("exam"));
			}
			pst.close();
			rs.close();
			conn.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
		return v;
	}
	
	public boolean delete(){
		boolean b=false;
		String sql="delete from score where rno=?";
		try{
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setInt(1, vo.getRno());
			int cnt = pst.executeUpdate();
			if(cnt>0) b=true;
			
			pst.close();
			conn.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
		return b;
	}
	
	public ScoreVo getVo(){
		return vo;
	}
	public void setVo(ScoreVo vo){
		this.vo = vo;
	}
}